﻿using BCCommon;
using BCCommon.PaymentEnums;
using BCEntity.Common.Payment.AliPayBill;
using BCEntity.Common.Payment.AliPayBill.AliPayBillDetail;
using BCEntity.Common.Payment.AliPayBill.AliPayBillStatistics;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;

namespace BCData.Common.Payment.AliPayBill
{
    public class AliPayBillData : IAliPayBillData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY_EXISTS = @"SELECT 1 FROM AliPayBillStatistics WHERE TradeDate=?TradeDate AND BusinessType=?BusinessType AND AppName=?AppName";
        private const string QUERY_DETAILS = @"SELECT AliPayBillDetailId,TradeDate,TradeTime,AliPayOrderNo,PaymentRecordId,AppName,AppId,MchId,BusinessType,BusinessTypeStr,
                                                    CompleteTime,ShopName,ShopNo,GoodsName,Operater,DeviceNo,OrderAmount,CouponAmount,PaySingleAmount,AliUserNo,
                                                    AliJiFenAmount,AliDiscountAmount,MchDiscountAmount,VolumeDiscountAmount,VolumeName,MchRedPackageAmount,CardAmount,
                                                    ApplyRefundNo,FeeRate,FeeRateStr,HandlingFee,ActualReceiveAmount,FeeRateRemark,CreateTime,IsSettle
                                               FROM AliPayBillDetail";
        private const string QUERY_STAT = @"SELECT AliPayBillStatisticsId,TradeDate,TradeOrderCount,RefundOrderCount,BusinessType,BusinessTypeStr,AppId,AppName,ShopNo,ShopName,OrderTotal,PayOrderTotal,
                                                HandlingFeeTotal,AliDiscountTotal,ActualReceiveTotal,MchDiscountTotal,CardTotal,PaidInNet,CreateTime
                                            FROM AliPayBillStatistics";
        private const string QUERY_STAT_SUM = @"SELECT SUM(TradeOrderCount) AS TradeOrderCount,SUM(RefundOrderCount) AS RefundOrderCount,SUM(OrderTotal) AS OrderTotal,
                                                    SUM(PayOrderTotal) AS PayOrderTotal,SUM(HandlingFeeTotal) AS HandlingFeeTotal,SUM(AliDiscountTotal) AS AliDiscountTotal,
                                                    SUM(ActualReceiveTotal) AS ActualReceiveTotal,SUM(MchDiscountTotal) AS MchDiscountTotal,SUM(CardTotal) AS CardTotal,
                                                    SUM(PaidInNet) AS PaidInNet,MIN(BusinessTypeStr) AS BusinessTypeStr, MIN(BusinessType) AS BusinessTypeStr
                                            FROM AliPayBillStatistics";
        public AliPayBillData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }

        public async Task<bool> SomeDayBillExists(DateTime tradeDate, AliPayBillType aliPayBillType,SourceAppType appType)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TradeDate", tradeDate.Date, DbType.Date);
            parameters.Add("?BusinessType", aliPayBillType.ToString(), DbType.AnsiString);
            parameters.Add("?AppName", appType.ToString(), DbType.AnsiString);
            return await this.databaseContext.Slave.ExecuteScalarAsync(QUERY_EXISTS, parameters) != null;
        }

        public async Task<bool> Insert(AliPayBillEntity billEntity)
        {
            List<DynamicParameters> dynamicParameters = new List<DynamicParameters>();
            string sql = SqlCommands.GetInsertSql("AliPayBillDetail", new string[] { "TradeDate","TradeTime","AliPayOrderNo","PaymentRecordId",
                                "AppName","AppId","MchId","BusinessType","BusinessTypeStr","CompleteTime","ShopName","ShopNo","GoodsName","Operater","DeviceNo",
                                "OrderAmount","CouponAmount","PaySingleAmount","AliUserNo","AliJiFenAmount","AliDiscountAmount","MchDiscountAmount",
                                "VolumeDiscountAmount","VolumeName","MchRedPackageAmount","CardAmount","ApplyRefundNo","FeeRate","FeeRateStr","HandlingFee",
                                "ActualReceiveAmount","FeeRateRemark" });
            foreach (var item in billEntity.Details)
            {
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("?TradeDate", item.TradeDate, DbType.Date);
                parameters.Add("?TradeTime", item.TradeTime, DbType.DateTime);
                parameters.Add("?AliPayOrderNo", item.AliPayOrderNo, DbType.AnsiString, size: 50);
                parameters.Add("?PaymentRecordId", item.PaymentRecordId, DbType.Int64, size: 20);
                parameters.Add("?AppName", item.AppName, DbType.AnsiString, size: 50);
                parameters.Add("?AppId", item.AppId, DbType.AnsiString, size: 50);
                parameters.Add("?MchId", item.MchId, DbType.AnsiString, size: 30);
                parameters.Add("?BusinessType", item.BusinessType, DbType.AnsiString, size: 30);
                parameters.Add("?BusinessTypeStr", item.BusinessTypeStr, DbType.AnsiString, size: 20);
                parameters.Add("?CompleteTime", item.CompleteTime, DbType.DateTime);
                parameters.Add("?ShopName", item.ShopName, DbType.AnsiString, size: 50);
                parameters.Add("?ShopNo", item.ShopNo, DbType.AnsiString, size: 50);
                parameters.Add("?GoodsName", item.GoodsName, DbType.AnsiString, size: 200);
                parameters.Add("?Operater", item.Operater, DbType.AnsiString, size: 30);
                parameters.Add("?DeviceNo", item.DeviceNo, DbType.AnsiString, size: 50);
                parameters.Add("?OrderAmount", item.OrderAmount, DbType.Decimal);
                parameters.Add("?CouponAmount", item.CouponAmount, DbType.Decimal);
                parameters.Add("?PaySingleAmount", item.PaySingleAmount, DbType.Decimal);
                parameters.Add("?AliUserNo", item.AliUserNo, DbType.AnsiString, size: 50);
                parameters.Add("?AliJiFenAmount", item.AliJiFenAmount, DbType.Decimal);
                parameters.Add("?AliDiscountAmount", item.AliDiscountAmount, DbType.Decimal);
                parameters.Add("?MchDiscountAmount", item.MchDiscountAmount, DbType.Decimal);
                parameters.Add("?VolumeDiscountAmount", item.VolumeDiscountAmount, DbType.Decimal);
                parameters.Add("?VolumeName", item.VolumeName, DbType.AnsiString, size: 30);
                parameters.Add("?MchRedPackageAmount", item.MchRedPackageAmount, DbType.Decimal);
                parameters.Add("?CardAmount", item.CardAmount, DbType.Decimal);
                parameters.Add("?ApplyRefundNo", item.ApplyRefundNo, DbType.AnsiString, size: 50);
                parameters.Add("?FeeRate", item.FeeRate, DbType.Int32, size: 10);
                parameters.Add("?FeeRateStr", item.FeeRateStr, DbType.AnsiString, size: 20);
                parameters.Add("?FeeRateRemark", item.FeeRateRemark, DbType.String);
                parameters.Add("?HandlingFee", item.HandlingFee, DbType.Decimal);
                parameters.Add("?ActualReceiveAmount", item.ActualReceiveAmount, DbType.Decimal);
                dynamicParameters.Add(parameters);
            }
            var result = await this.databaseContext.Master.ExecuteAsync(sql, dynamicParameters, databaseContext.Transaction) > 0;
            if (!result)
            {
                throw new ArgumentException("插入支付宝账单记录失败!");
            }

            dynamicParameters = new List<DynamicParameters>();
            sql = SqlCommands.GetInsertSql("AliPayBillStatistics", new string[] { "TradeDate", "TradeOrderCount", "RefundOrderCount","BusinessType","BusinessTypeStr",
                                        "AppId","AppName", "ShopNo", "ShopName", "OrderTotal", "PayOrderTotal", "HandlingFeeTotal", "AliDiscountTotal",
                                        "ActualReceiveTotal", "MchDiscountTotal", "CardTotal", "PaidInNet" });
            foreach (var item in billEntity.Statistics)
            {
                DynamicParameters statParameters = new DynamicParameters();
                statParameters.Add("?TradeDate", item.TradeDate, DbType.Date);
                statParameters.Add("?TradeOrderCount", item.TradeOrderCount, DbType.Int32, size: 10);
                statParameters.Add("?RefundOrderCount", item.RefundOrderCount, DbType.Int32, size: 10);
                statParameters.Add("?BusinessType", item.BusinessType, DbType.AnsiString, size: 30);
                statParameters.Add("?BusinessTypeStr", item.BusinessTypeStr, DbType.AnsiString, size: 20);
                statParameters.Add("?AppName", item.AppName, DbType.AnsiString, size: 50);
                statParameters.Add("?AppId", item.AppId, DbType.AnsiString, size: 50);
                statParameters.Add("?ShopName", item.ShopName, DbType.AnsiString, size: 50);
                statParameters.Add("?ShopNo", item.ShopNo, DbType.AnsiString, size: 50);
                statParameters.Add("?OrderTotal", item.OrderTotal, DbType.Decimal);
                statParameters.Add("?PayOrderTotal", item.PayOrderTotal, DbType.Decimal);
                statParameters.Add("?HandlingFeeTotal", item.HandlingFeeTotal, DbType.Decimal);
                statParameters.Add("?AliDiscountTotal", item.AliDiscountTotal, DbType.Decimal);
                statParameters.Add("?ActualReceiveTotal", item.ActualReceiveTotal, DbType.Decimal);
                statParameters.Add("?MchDiscountTotal", item.MchDiscountTotal, DbType.Decimal);
                statParameters.Add("?CardTotal", item.CardTotal, DbType.Decimal);
                statParameters.Add("?PaidInNet", item.PaidInNet, DbType.Decimal);
                dynamicParameters.Add(statParameters);
            }
            return await this.databaseContext.Master.ExecuteAsync(sql, dynamicParameters, databaseContext.Transaction) > 0;
            
        }

        public async Task<(IEnumerable<AliPayBillDetailEntity>, AliPayBillStatisticsEntity)> GetSomeDayBill(DateTime tradeDate, AliPayBillType? aliPayBillType)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("TradeDate=?TradeDate");
            parameters.Add("?TradeDate", tradeDate.Date, DbType.Date);
            if (aliPayBillType.HasValue)
            {
                wheres.Add("BusinessType=?BusinessType");
                parameters.Add("?BusinessType", aliPayBillType.ToString(), DbType.AnsiString);
            }

            string sql = string.Format("{0} {2} ORDER BY TradeTime DESC; {1} {2}", QUERY_DETAILS, QUERY_STAT_SUM, wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "");

            return await this.databaseContext.Slave.QueryMultipleAsync<AliPayBillDetailEntity, AliPayBillStatisticsEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<AliPayBillDetailEntity>, AliPayBillStatisticsEntity)> GetSomeMonthBill(int year, int month, AliPayBillType? aliPayBillType)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("DATE_FORMAT(TradeDate,'%Y-%m')=DATE_FORMAT(STR_TO_DATE(?TradeDate,'%Y-%m-%d'),'%Y-%m')");
            parameters.Add("?TradeDate", new DateTime(year, month, 1), DbType.Date);
            if (aliPayBillType.HasValue)
            {
                wheres.Add("BusinessType=?BusinessType");
                parameters.Add("?BusinessType", aliPayBillType.ToString(), DbType.AnsiString);
            }
            string sql = string.Format(@"{0} {2} ORDER BY TradeTime DESC; {1} {2}",
                QUERY_DETAILS,
                QUERY_STAT_SUM,
                wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "");
            return await this.databaseContext.Slave.QueryMultipleAsync<AliPayBillDetailEntity, AliPayBillStatisticsEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<AliPayBillDetailEntity>, AliPayBillStatisticsEntity)> GetSomeYearBill(int year, AliPayBillType? aliPayBillType)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("DATE_FORMAT(TradeDate,'%Y')=?Year");
            parameters.Add("?Year", year, DbType.Int32);
            if (aliPayBillType.HasValue)
            {
                wheres.Add("BusinessType=?BusinessType");
                parameters.Add("?BusinessType", aliPayBillType.ToString(), DbType.AnsiString);
            }
            string sql = string.Format(@"{0} {2} ORDER BY TradeTime DESC; {1} {2}",
                QUERY_DETAILS,
                QUERY_STAT_SUM,
                wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "");
            return await this.databaseContext.Slave.QueryMultipleAsync<AliPayBillDetailEntity, AliPayBillStatisticsEntity>(sql, parameters);
        }

        public async Task<AliPayBillDetailEntity> GetEntityByPaymentRecordId(long paymentRecordId)
        {
            string sql = string.Format("{0} WHERE PaymentRecordId=?PaymentRecordId ORDER BY CreateTime DESC LIMIT 1", QUERY_DETAILS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?PaymentRecordId", paymentRecordId, DbType.Int64);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<AliPayBillDetailEntity>(sql, parameters);
        }
    }
}
